package com.situ.stl.dao;

import com.situ.stl.entity.Course;
import java.util.List;

import com.situ.stl.vo.CourseVo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/** 
 * Dao层 - 课程
 */
@Mapper 
public interface CourseMapper{

    /** 
     * Insert
     */
    @Insert( value = "insert into `course` ( `name`, `time`, `length`, `max_count`, `room`, `photo`, `coach_id`) value ( #{name}, #{time}, #{length}, #{maxCount}, #{room}, #{photo}, #{coachId})") 
    @Options( useGeneratedKeys = true, keyColumn = "id", keyProperty = "id") 
    int insert( Course course) ;

    /** 
     * Delete
     */
    @Delete( value = "delete from `course` where `id` = #{id}") 
    int delete( Integer id) ;

    /** https://gitee.com/bobzyh/stl
     * Update
     */
    @Update( value = {
        "<script>",
        "update `course`",
        "<set>",
        "<if test='name!=null'> `name` = #{name},</if>",
        "<if test='time!=null'> `time` = #{time},</if>",
        "<if test='length!=null'> `length` = #{length},</if>",
        "<if test='maxCount!=null'> `max_count` = #{maxCount},</if>",
        "<if test='room!=null'> `room` = #{room},</if>",
        "<if test='photo!=null'> `photo` = #{photo},</if>",
        " `coach_id` = #{coachId},",
        "</set>",
        " where `id` = #{id}",
        "</script>"}) 
    int update( Course course) ;

    /** 
     * Select All
     */
    @Select( value = "select * from `course`") 
    @Results( value = {
        @Result( column = "id", property = "id", id = true),
        @Result( column = "name", property = "name"),
        @Result( column = "time", property = "time"),
        @Result( column = "length", property = "length"),
        @Result( column = "max_count", property = "maxCount"),
        @Result( column = "room", property = "room"),
        @Result( column = "photo", property = "photo"),
        @Result( column = "coach_id", property = "coachId"),
        @Result( column = "coach_id", property = "coach", one = @One( select = "com.situ.stl.dao.CoachMapper.selectByPrimaryKey"))})
    List<Course> selectAll() ;

    /** 
     * Select by primary key
     */
    @Select( value = "select * from `course` where `id` = #{id}") 
    @Results( value = {
        @Result( column = "id", property = "id", id = true),
        @Result( column = "name", property = "name"),
        @Result( column = "time", property = "time"),
        @Result( column = "length", property = "length"),
        @Result( column = "max_count", property = "maxCount"),
        @Result( column = "room", property = "room"),
        @Result( column = "photo", property = "photo"),
        @Result( column = "coach_id", property = "coachId"),
        @Result( column = "coach_id", property = "coach", one = @One( select = "com.situ.stl.dao.CoachMapper.selectByPrimaryKey"))})
    Course selectByPrimaryKey( int id) ;

    /** 
     * Select
     */
    @Select( value = {
        "<script>",
        "select course.*, count(reserve.id) reserve_count from `course`",
        "left join reserve on course.id = reserve.course_id",
        "<where>",
        "<if test='name!=null and name.length>0'> and `name` like '%${name}%'</if>",
        "<if test='time!=null'> and `time` = #{time}</if>",
        "<if test='length!=null'> and `length` = #{length}</if>",
        "<if test='maxCount!=null'> and `max_count` = #{maxCount}</if>",
        "<if test='room!=null and room.length>0'> and `room` like '%${room}%'</if>",
        "<if test='photo!=null and photo.length>0'> and `photo` like '%${photo}%'</if>",
        "<if test='coachId!=null'> and `coach_id` = #{coachId}</if>",
        "and reserve.status=1",
        "</where>",
        "group by course.id",
        "order by time desc",
        "</script>"}) 
    @Results( value = {
        @Result( column = "id", property = "id", id = true),
        @Result( column = "name", property = "name"),
        @Result( column = "time", property = "time"),
        @Result( column = "length", property = "length"),
        @Result( column = "max_count", property = "maxCount"),
        @Result( column = "room", property = "room"),
        @Result( column = "photo", property = "photo"),
        @Result( column = "coach_id", property = "coachId"),
        @Result( column = "coach_id", property = "coach", one = @One( select = "com.situ.stl.dao.CoachMapper.selectByPrimaryKey"))})
    List<CourseVo> select(Course course) ;

    @Select({
            "select id from course",
            "where coach_id=#{coachId}",
    })
    List<Course> selectByCoachId(int coachId);
}